package store.dao;

import store.po.Restock;
import store.utils.Pager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class RestockDAO {

    /**
     * 根据参数id查询供应商表t_restock，将结果封装成Restock返回
     *
     * @param id
     * @return
     * @throws Exception
     */
    public Restock findById(int id) throws Exception {
        Restock r = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("select * from t_restock where id=? ");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                r = new Restock();
                r.setId(rs.getInt("id"));
                r.setGoodsId(rs.getInt("goods_id"));
                r.setSupplierId(rs.getInt("supplier_id"));
                r.setPrice(rs.getDouble("price"));
                r.setStock(rs.getInt("stock"));
                r.setTradeDate(rs.getString("trade_date"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
        return r;
    }

    /**
     * 根据参数goodsName,supplier模糊查询表t_restock，将结果封装成List返回
     *
     * @param gName supplier
     * @return
     * @throws Exception
     */
    public List<Restock> findRestockes(String gName, String sName, String sDate, String eDate, Pager pager) throws Exception {
        List<Restock> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("SELECT count(r.id) as total FROM t_restock as r  " +
                    "inner join t_supplier as s on r.supplier_id=s.id " +
                    "inner join t_goods as g on r.goods_id=g.id " +
                    "where g.name like ? and s.name like ? and trade_date between ? and ?;");
            ps.setString(1, "%" + gName + "%");
            ps.setString(2, "%" + sName + "%");
            ps.setString(3, sDate);
            ps.setString(4, eDate);
            rs=ps.executeQuery();
            if(rs.next()){
                pager.setTotal(rs.getInt("total"));
            }

            ps = con.prepareStatement("SELECT r.*,s.name as sname,g.name as gname FROM t_restock as r  " +
                    "inner join t_supplier as s on r.supplier_id=s.id " +
                    "inner join t_goods as g on r.goods_id=g.id " +
                    "where g.name like ? and s.name like ? and trade_date between ? and ? limit ?,?;");
            ps.setString(1, "%" + gName + "%");
            ps.setString(2, "%" + sName + "%");
            ps.setString(3, sDate);
            ps.setString(4, eDate);
            ps.setInt(5,(pager.getCurrentPage()-1)*pager.getPageSize());
            ps.setInt(6,pager.getPageSize());
            rs = ps.executeQuery();
            while (rs.next()) {
                Restock r = new Restock();
                r.setId(rs.getInt("id"));
                r.setGoodsId(rs.getInt("goods_id"));
                r.setSupplierId(rs.getInt("supplier_id"));
                r.setPrice(rs.getDouble("price"));
                r.setStock(rs.getInt("stock"));
                r.setTradeDate(rs.getString("trade_date"));
                r.setGoodsName(rs.getString("gname"));
                r.setSupplier(rs.getString("sname"));
                list.add(r);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
        return list;
    }

    /**
     * 将Restock类型参数r的值保存成t_restock表中的一条记录
     *
     * @param r
     * @throws Exception
     */
    public void save(Restock r) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("insert into t_restock value (null ,?,?,?,?,?)");
            ps.setInt(1, r.getGoodsId());
            ps.setInt(2, r.getSupplierId());
            ps.setDouble(3, r.getPrice());
            ps.setInt(4, r.getStock());
            ps.setString(5, r.getTradeDate());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
    }

    /**
     * 将Restock对象r更新到t_restock表中
     *
     * @param r
     * @throws Exception
     */
    public void update(Restock r) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("update t_restock set goods_id=?,supplier_id=?,price=?,stock=?,trade_date=? where id=?");
            ps.setInt(1, r.getGoodsId());
            ps.setInt(2, r.getSupplierId());
            ps.setDouble(3, r.getPrice());
            ps.setInt(4, r.getStock());
            ps.setString(5, r.getTradeDate());
            ps.setInt(6, r.getId());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
    }

    /**
     * 根据id删除t_restock表中对应的记录
     *
     * @param id
     * @throws Exception
     */
    public void delete(int id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            ps = con.prepareStatement("delete  from t_restock where id=?");
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
    }
}
